const express = require('express')
const router = express.Router()
//token
const jwt = require('jsonwebtoken')
//秘钥
const config = require('./secert')
//连接数据库   sqlFn('sql',[],res=>{})
const sqlFn = require('./mysql')
//图片上传支持的模块
const multer= require('multer')
const fs = require('fs')

// 导入mockjs(用来模拟数据)
const Mock = require("mockjs")


//测试接口
router.get('/', (req, res) => {
    res.send('hello')
})

//登录接口
/**
 * 语法：
 * 如60，"2 days"，"10h"，"7d"，Expiration time，过期时间
 *  jwt.sign({},'秘钥','过期时间,{expiresIn:20*1,'1day''1h'}')
 */

/**
 * 登录 login
 * 接受字段：username、password
 * 测试：postman
 */
router.post('/login', (req, res) => {
    let {
        username,
        password
    } = req.body
    //请求数据库
    let sql = "select * from userinfo where username=? and password=?";
    let arr = [username, password]
    sqlFn(sql, arr, result => {
        if (result.length > 0) {
            let token = jwt.sign({
                username: result[0].username,
                name: result[0].name,
                id: result[0].id
            }, config.jwtSecert, {
                expiresIn: 20 * 1
            })
            res.send({
                status: 200,
                data: token
            })
        } else {
            res.send({
                status: 404,
                msg: '信息错误'
            })
        }

    })
})

/**
 * 注册接口 /register
 */
router.post("/register", (req, res) => {
    const {
        username,
        password
    } = req.body;
    const sql = "insert into userinfo values(null,?,?)";
    const arr = [username, password];
    sqlFn(sql, arr, (result) => {
        if (result.affectedRows > 0) {
            res.send({
                msg: "注册成功",
                status: 200
            })
        } else {
            res.status(401).json({
                errors: "用户名密码错误"
            })
        }
    })
})

/**
 * 商品列表：获取分页 {total:'',arr:[{},{},{}],pagesize:10,}
 * 参数：page 页码，pagesize 每页显示的记录数
 */
router.get('/projectList', (req, res) => {
    
    const page = req.query.page || 1;
    const pageSize = req.query.pageSize || 10; // 默认每页显示10条记录
    const sw = JSON.parse(req.query.searchWords)
   
    let cond = '';
    if (sw.device_number != '') cond += ` and  device_number like '%${sw.device_number}%'`;
    if (sw.device_number != '') cond += ` and device_number like '%${sw.device_number}%'`;
    if (sw.devic != '') cond += ` and devic like '%${sw.devic}%'`;
    if (sw.device_item != '') cond += ` and device_item like '%${sw.device_item}%'`;
    if (sw.equipment_name != '') cond += ` and equipment_name like '%${sw.equipment_name}%'`;
    if (sw.specification_and_model != '') cond += ` and specification_and_model like '%${sw.specification_and_model}%'`;
    if (sw.equipment_classification != '') cond += ` and equipment_classification like '%${sw.equipment_classification}%'`;
    if (sw.shaft_power != '') cond += ` and shaft_power like '%${sw.shaft_power}%'`;
    if (sw.manufacturer != '') cond += ` and manufacturer like '%${sw.manufacturer}%'`;
    if (sw.rated_flow_rate != '') cond += ` and rated_flow_rate like '%${sw.rated_flow_rate}%'`;
    // if (sw.image != '') cond += ` and image like '%${sw.image}%'`;
    if (sw.biaos != '') cond += ` and biaos like '%${sw.biaos}%'`;
    const sqlLen = "select count(*) as len from project where 1=1" + cond;
    sqlFn(sqlLen, null, data => {
        let len = data[0].len;
        let sql = "select * from project where 1=1" + cond;
        sql += " order by id desc limit " + pageSize + " offset " + (page - 1) * pageSize;
        sqlFn(sql, null, result => {
            if (result.length > 0) {
                res.send({
                    status: 200,
                    data: result,
                    pageSize: pageSize,
                    total: len
                })
            } else {
                res.send({
                    status: 500,
                    msg: "暂无数据"
                })
            }
        })
    })
})

/**
 * 商品查询接口 search
 * 参数：search
 */
router.get("/search", (req, res) => {
    var search = req.query.search;
    const sql = "select * from project where concat(`devic`,`equipment_name`,`specification_and_model`) like '%" + search + "%'";
    sqlFn(sql, null, (result) => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: "暂无数据"
            })
        }
    })
})




/**
 * 类目选择
 * 接口说明：接口不同的参数cid 返回不同的类目数据 后台接受变量：id  
 * {id: cid}
 */
router.get("/backend/itemCategory/selectItemCategoryByParentId", (req, res) => {
    const id = req.query.id || 1;
    const sql = "select * from category where id=?"
    var arr = [id];
    sqlFn(sql, arr, result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: "暂无数据"
            })
        }
    })
})




/**
 * 上传图片 post请求 upload
 * 说明：
 *  1.后台安装 multer 模块   同时引入fs模块
 *  2.router.js入口文件导入模块
 *      const fs=require('fs')
        const multer=require('multer')
 *  3.上传图片 不能跨域  需要配置cors  index.js导入文件 并配置cors跨域
 * 
 */

var storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, "./upload/")
    },
    filename: function (req, file, cb) {
        cb(null, Date.now() + "-" + file.originalname)
    }
})

var createFolder = function (folder) {
    try {
        fs.accessSync(folder);
    } catch (e) {
        fs.mkdirSync(folder);
    }
}

var uploadFolder = './upload/';
createFolder(uploadFolder);
var upload = multer({
    storage: storage
});


// 图片上传 post请求
router.post('/upload', upload.single('file'), function (req, res, next) {
    var file = req.file;
    console.log('文件类型：%s', file.mimetype);
    console.log('原始文件名：%s', file.originalname);
    console.log('文件大小：%s', file.size);
    console.log('文件保存路径：%s', file.path);
    res.json({
        res_code: '0',
        name: file.originalname,
        url: file.path
    });
});



/**
 * 商品添加
 * 参数： title cid  category sellPoint price num desc paramsInfo image
 */
 router.get("/backend/item/insertTbItem", (req, res) => {
    /**
     * 获取参数
     */
    var title = req.query.title || "";
    var cid = req.query.cid || "";
    var category = req.query.category || "";
    var sellPoint = req.query.sellPoint || "";
    var price = req.query.price || "";
    var num = req.query.num || "";
    var desc = req.query.descs || "";
    var paramsInfo = req.query.paramsInfo || "";
    var image = req.query.image || "";


    var device_encoding = req.query.device_encoding || "";
    var device_name = req.query.device_name || "";
    var device_model = req.query.device_model || "";
    var device_type = req.query.device_type || "";
    var device_status = req.query.device_status || "";
    var device_location = req.query.device_location || "";
    var device_department = req.query.device_department || "";

    const sql = "insert into project(title, image, sellPoint, price, cid,category, num, descs,paramsInfo,device_encoding,device_name,device_model,device_type,device_status,device_location,device_department) \
    values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    var arr = [title, image, sellPoint, price, cid,category, num, desc,paramsInfo, device_encoding,device_name,device_model,device_type,device_status,device_location,device_department];
    sqlFn(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "添加成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "添加失败"
            })
        }
    })
})


/**
 * 商品删除接口 id
 */
 router.get("/backend/item/deleteItemById", (req, res) => {
    var id = req.query.id;
    const sql = "delete from project where id=?"
    const arr = [id];
    sqlFn(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})


/**
 * 批量删除 batchDelete  idArr Array  id标识
 * sql = "delete from A where id in (1,2,3)"  
 */
 router.get("/batchDelete", (req, res) => {
    let arr=req.query.idArr;//[] 数组格式 需要传递数据是 离散的数字格式 
    // const sql =`delete from project where id in (?)`;
    let sql='';
    function fun(arr){//sql =`delete from project where id in (101,102,103)`;
        sql=`delete from project where id in (`
            for(let i=0;i<arr.length;i++){
                sql+= arr[i]+',' //101,102,
            }
            sql=sql.slice(0,-1) 
            sql=sql+')'
            // console.log(sql);
    }
    fun(arr)
    sqlFn(sql, null, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})




/**
 * 修改商品
 */

 router.get("/backend/item/updateTbItem", (req, res) => {
    var id = req.query.id;
    var params = req.query;
    console.log(params);

    // 删除不需要的字段
    delete params.id;

    // 构建 SQL 语句和参数数组
    var sql = "UPDATE project SET ";
    var values = [];
    var setClauses = [];

    for (var key in params) {
        if (params[key] !== undefined && params[key] !== '') {
            setClauses.push(`${key} = ?`);
            values.push(params[key]);
        }
    }

    sql += setClauses.join(", ");
    sql += " WHERE id = ?";
    values.push(id);
    sqlFn(sql, values, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "修改成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "修改失败"
            })
        }
    })
})



/**
 * 规格参数列表  参数 page
 */
 router.get("/backend/itemParam/selectItemParamAll", (req, res) => {
    const page = req.query.page || 1;
    const sqlLen = "select * from params where id";
    sqlFn(sqlLen, null, data => {
        let len = data.length;
        const sql = "select * from params order by id desc limit 8 offset " + (page - 1) * 8;
        sqlFn(sql, null, result => {
            if (result.length > 0) {
                res.send({
                    status: 200,
                    data: result,
                    pageSize:8,
                    total:len
                })
            } else {
                res.send({
                    status: 500,
                    msg: "暂无数据"
                })
            }
        })
    })
})

/**
 * 规格参数  模糊查询  参数：search
 */
router.get("/params/search", (req, res) => {
    var search =req.query.search;
    const sql = "select * from params where concat(`paramData`) like '%" + search + "%'";
    sqlFn(sql, [search], result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: '暂无数据'
            })
        }
    })
})

/**
 * 规格参数 添加 
 * 参数：itemCatId,content,specsName
 */
 router.get("/backend/itemParam/insertItemParam", (req, res) => {
    var itemCatId = req.query.itemCatId;
    var specsName = req.query.specsName;
    var paramsContent = req.query.content;
    // console.log(itemCatId, paramsContent,specsName);
    var sql = "insert into params(itemCatId, specsName,paramData) values (?,?,?)";
    sqlFn(sql, [itemCatId, specsName,paramsContent], result => {
        // console.log('result:',result);
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "添加成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "添加失败"
            })
        }
    })
})


/**
 * 修改规格参数 cid content id  specsName
 */
router.get("/update/category", (req, res) => {
    var id = req.query.id;
    var content = req.query.content;
    var cid = req.query.cid;
    var specsName = req.query.specsName;
    var sql = "update params set paramData=? ,itemCatId=?,specsName=? where id=?";
    // var sql = "update params set paramData=? where itemCatId=?";
    // sqlFn(sql, [content, cid], result => {
    sqlFn(sql, [content, cid, specsName, id], result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "修改成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "修改失败"
            })
        }
    })
})

/**
 * 规格参数删除
 */
router.get("/params/delete", (req, res) => {
    var id = req.query.id;
    const sql = "delete from params where id=?"
    const arr = [id];
    sqlFn(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})

/**
 * 配件列表：获取分页 {total:'',arr:[{},{},{}],pagesize:10,}
 * 参数：page 页码，pagesize 每页显示的记录数
 */
router.get('/accessoryList', (req, res) => {
    
    const page = req.query.page || 1;
    const pageSize = req.query.pageSize || 10; // 默认每页显示10条记录
    const sw = JSON.parse(req.query.searchWords)
   
    let cond = '';
    if (sw.equipment != '') cond += ` and equipment like '%${sw.equipment}%'`;
    if (sw.Name_of_PM_Planner_Group != '') cond += ` and Name_of_PM_Planner_Group like '%${sw.Name_of_PM_Planner_Group}%'`;
    if (sw.Component_encoding != '') cond += ` and Component_encoding like '%${sw.Component_encoding}%'`;
    if (sw.Code_material_description != '') cond += ` and Code_material_description like '%${sw.Code_material_description}%'`;
    // if (sw.biaos != '') cond += ` and biaos like '%${sw.biaos}%'`;
    const sqlLen = "select count(*) as len from accessory where 1=1" + cond;
    sqlFn(sqlLen, null, data => {
        let len = data[0].len;
        let sql = "select * from accessory where 1=1" + cond;
        sql += " order by id desc limit " + pageSize + " offset " + (page - 1) * pageSize;
        sqlFn(sql, null, result => {
            if (result.length > 0) {
                res.send({
                    status: 200,
                    data: result,
                    total: len
                })
            } else {
                res.send({
                    status: 500,
                    msg: "暂无数据"
                })
            }
        })
    })
})

/**
 * 规格参数 添加 
 * 参数：itemCatId,content,specsName
 */
 router.get("/backend/insertAccessory", (req, res) => {
    var itemCatId = req.query.itemCatId;
    var specsName = req.query.specsName;
    var paramsContent = req.query.content;
    // console.log(itemCatId, paramsContent,specsName);
    var sql = "insert into accessory(biaos, device_no, code, name) values (?,?,?)";
    sqlFn(sql, [biaos, device_no, code, name], result => {
        // console.log('result:',result);
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "添加成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "添加失败"
            })
        }
    })
})

/**
 * 配件删除接口 id
 */
 router.get("/backend/deleteAccessory", (req, res) => {
    var id = req.query.id;
    const sql = "delete from accessory where id=?"
    const arr = [id];
    sqlFn(sql, arr, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})

/**
 * 修改配件
 */

 router.get("/backend/updateAccessory", (req, res) => {
    var id = req.query.id;
    var params = req.query;
    console.log(params);

    // 删除不需要的字段
    delete params.id;

    // 构建 SQL 语句和参数数组
    var sql = "UPDATE accessory SET ";
    var values = [];
    var setClauses = [];

    for (var key in params) {
        if (params[key] !== undefined && params[key] !== '') {
            setClauses.push(`${key} = ?`);
            values.push(params[key]);
        }
    }

    sql += setClauses.join(", ");
    sql += " WHERE id = ?";
    values.push(id);
    sqlFn(sql, values, result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "修改成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "修改失败"
            })
        }
    })
})


/**
 * 规格参数类目结构数据获取 cid
 */
 router.get("/category/data", (req, res) => {
    var cid = req.query.cid;
    var sql = "select * from params where itemCatId=?";
    sqlFn(sql, [cid], result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: "暂无数据"
            })
        }
    })
})


/**
 * 内容分类管理 导航
 */
 router.get("/content/selectContentCategoryByParentId", (req, res) => {
    const id = req.query.id || 1;
    const sql = "select * from content where id=?";
    sqlFn(sql, [id], result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: "暂无数据"
            })
        }
    })
})

/**
 * 广告分类管理 增加子导航
 */
router.get("/content/insertContentCategory", (req, res) => {
    const pid = req.query.pid;
    const name =req.query.name;
    const currentId = Math.floor(Math.random() * 10000)
    const sql = "insert into content values (?,?,?)"
    sqlFn(sql, [pid, name, currentId], result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "添加成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "添加失败"
            })
        }
    })
})

/**
 *  广告分类管理 修改子导航
 */
router.get("/content/updateContentCategory", (req, res) => {
    const pid = req.query.pid;
    const name = req.query.name;
    const sql = "update content set name=? where pid=?"
    sqlFn(sql, [name, pid], result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "修改成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "修改失败"
            })
        }
    })
})

/**
 *广告分类管理 删除子导航 pid
 */
router.get("/content/deleteContentCategoryById", (req, res) => {
    const pid = req.query.pid;
    const sql = "delete from content where pid=?"
    sqlFn(sql, [pid], result => {
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})

/**
 * 广告分类管理 内容增加
 */
router.get("/content/insertTbContent",(req,res) =>{
    const pid = req.query.pid;
    const name = req.query.name;
    const contentUrl = req.query.url;
    const image = req.query.image;
    const sql = "insert into contentinfo values(null,?,?,?,?)";
    sqlFn(sql,[pid,name,contentUrl,image],result =>{
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "添加成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "添加失败"
            })
        }
    })
})


/**
* 广告分类管理 内容删除
*/

router.get("/content/deleteContentByIds",(req,res) =>{
    const id = req.query.id;
    const sql = "delete from contentinfo where id=?"
    sqlFn(sql,[id],result =>{
        if (result.affectedRows > 0) {
            res.send({
                status: 200,
                msg: "删除成功"
            })
        } else {
            res.send({
                status: 500,
                msg: "删除失败"
            })
        }
    })
})

/**
 * 内容分类管理 内容查询
 */
router.get("/content/selectTbContentAllByCategoryId", (req, res) => {
    const pid = req.query.pid;
    const sql = "select * from contentinfo where pid=?"
    sqlFn(sql, [pid], result => {
        if (result.length > 0) {
            res.send({
                status: 200,
                result
            })
        } else {
            res.send({
                status: 500,
                msg: "暂无数据"
            })
        }
    })
})


// 测试mockjs数据
router.get("/test",(req, res)=>{
    // 使用Mock生成数据
    let data = Mock.mock({
        info: '我是一个单纯的对象',
        status: 200,
        // 生成list字段：数组类型，内容是6个数据={}
        "list|6":[{
            "id|+1": 100,  // id自增
            "flag|1": true,
            "city|2": {  // 获取两个城市数据
                "310000": "上海市",
                "320000": "江苏省",
                "330000": "浙江省",
                "340000": "安徽省"
            },
            "arr|1": [  // 一次获取一个数据值，依次获取
                "AMD",
                "CMD",
                "UMD"
            ],
            "desc": '@cword(20,80)',  // 随机汉字
            "imgurl": '@Image()'
        }]
    })

    res.send(data)
})


module.exports = router
